package com.gljx.web.controller;

import com.gljx.util.GetFid;
import com.gljx.util.GetFnumber;
import com.gljx.util.ImportExecl;
import com.gljx.util.TimeFormat;
import com.gljx.web.Entity.Student;
import com.gljx.web.Entity.StudentImport;
import com.gljx.web.Entity.User;
import com.gljx.web.dao.StudentMapper;
import com.gljx.web.service.StudentImportService;
import com.gljx.web.service.SubjectOneResultService;
import com.gljx.web.service.SubjectThreeResultService;
import com.gljx.web.service.SubjectTwoResultService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.util.*;

@Controller
@RequestMapping("/")
public class StudentExamResultImpotrExcelController {
	@Autowired
	private SubjectOneResultService subjectOneResultService;

	@Autowired
	private SubjectTwoResultService subjectTwoResultService;

	@Autowired
	private SubjectThreeResultService subjectThreeResultService;

	@Autowired
	private StudentImportService studentImportService;

	@Autowired
	private StudentMapper studentMapper;

	private ImportExecl poi = new ImportExecl();


	@RequestMapping(value="/studentExamResultUpload",method=RequestMethod.POST)
	@ResponseBody
	public Object subjectOneResultUpload(@RequestParam(value="file",required=false)MultipartFile file,
										 HttpServletRequest request,HttpServletResponse response)throws Exception{
		User user = (User) request.getSession().getAttribute("user");
		StudentImport stu = new StudentImport();
		String filesFileName=file.getOriginalFilename();
		try {
			uploadExcel(request,file, filesFileName);// 上传
		} catch (Exception e) {
			e.printStackTrace();
		}
		Map<String, List<StudentImport>> map = new HashMap<>();
		//System.out.println("文件名字: "+request.getServletContext().getRealPath("/")+ "xls" + File.separatorChar + filesFileName);
		//读取
		try {
			map=poi.show(
					request.getServletContext().getRealPath("/")+ "xls" + File.separatorChar + filesFileName
			);
		}catch (Exception e){
			e.printStackTrace();
			return -1;
		}
		if(map.size() == 0){//没有可以导入的数据！";
			return null;
		}else{
			//获取当前修改时间
			String createTime = TimeFormat.timeFormat(new Date());

			String ks_Date = map.get("1").get(0).getDate();//考试的日期

			Map<String, String> student = new HashMap<>();
			stu.setLister(user.getFid());//把当前操作人的ID添加到制单人ID
			stu.setReviewer(user.getFid());//把当前操作人的ID添加到审核人ID
			// --单据号
			String fnumber="";//单据编号
			Map<String, String> maxMap = new HashMap<>();//maxMap用来存储科目考试结果的表
            //添加所有的考试时间
			List<String> testTime=new ArrayList<>();
            for(int i=1;i<=map.size();i++){
				List<StudentImport> stus = map.get("" + i + "");
				//countmap.put(i+"", stus.size()+"");
				for (int j = 0; j < stus.size(); j++) {
					testTime.add(map.get(""+i+"").get(j).getDate());
				}
            }
            //考试时间去重
          List<String> testTime2=new ArrayList<>();
			for (String s1 : testTime) {
				if (!testTime2.contains(s1)) {
					testTime2.add(s1);
				}
			}
        //为了避免内存浪费
			testTime=null;

//			System.out.println(fnumber);
			String []childerFid=new String[testTime2.size()];//子类所存储的父类的id FPARENTID
			String[] fnumberNum=new String[testTime2.size()];
			for(int i=0;i<testTime2.size();i++){
				student.put("lister", stu.getLister());// 制作人ID
				student.put("createDate", createTime);// 创建日期
				student.put("biangengren", stu.getLister());// 变更人ID
				if (map.get("1").get(0).getCourse().equals("科目一")) {
					student.put("from", "CT_DS_SUBJECTONERESULT");
					//获取上一个编码
                /*String number = subjectOneResultService.selectFnumber(user.getFcompanyid());*/
					//获取编码
					fnumber = GetFnumber.fnumbers("OF");
					fnumberNum[i]=fnumber;
				} else if (map.get("1").get(0).getCourse().equals("科目二")) {
					//获取上一个编码
                /*String number = subjectTwoResultService.selectFnumber(user.getFcompanyid());*/
					//获取编码
					fnumber = GetFnumber.fnumbers("TF");
					student.put("from", "CT_DS_SUBJECTTWORESULT");
					fnumberNum[i]=fnumber;
				} else if (map.get("1").get(0).getCourse().indexOf("科目三") >= 0) {
					//获取上一个编码
                /*String number = subjectThreeResultService.selectFnumber(user.getFcompanyid());*/
					//获取编码
					fnumber = GetFnumber.fnumbers("SF");
					fnumberNum[i]=fnumber;
					student.put("from", "CT_DS_SUBJECTTHREERESULT");
				} else {
					//获取上一个编码
                /*String number = subjectThreeResultService.selectFnumber(user.getFcompanyid());*/
					//获取编码
					fnumber = GetFnumber.fnumbers("SF");
					fnumberNum[i]=fnumber;
					// 抛出异常
					student.put("from", "CT_DS_SUBJECTTHREERESULT");
				}

				maxMap.put("from", student.get("from"));
				//maxMap.put("danjuType", danjuType);
				student.put("danju", fnumber);
				student.put("kaoshiDate", testTime2.get(i));// 考试日期取Excel的第一条记录的日期
				student.put("reviewer", stu.getReviewer());// 审核人ID
				student.put("gsId", user.getFcompanyid());// 公司ID
				student.put("billState", "5");//单据状态为5
				student.put("fid", GetFid.UUIDIntoFid());//随机生成的id
				childerFid[i]=student.get("fid");
				student.put("cartype",map.get("1").get(0).getType());//车型取Excel的第一条记录的车型
				studentImportService.addMain(student);
			}
			/*** 添加主表完成 */
//			String fid = "";
//			if (map.get("1").get(0).getCourse().equals("科目一")) {
//				fid = studentImportService.findBydanjuOne(fnumber);// FPARENTID
//				//System.out.println("主表ID：>>>>>>>>>>>>>>>>>>>>>>> "+fid);
//			} else if (map.get("1").get(0).getCourse().equals("科目二")) {
//				fid = studentImportService.findBydanjuTwo(fnumber);// FPARENTID
//			} else if (map.get("1").get(0).getCourse().indexOf("科目三")>=0) {
//				fid = studentImportService.findBydanjuThr(fnumber);// FPARENTID
//			} else {
//				// 抛出异常
//				fid = studentImportService.findBydanjuThr(fnumber);// FPARENTID
//			}
			// 拼接sql获取导入信息显示到页面
			StringBuffer buf = new StringBuffer();
			String strsql = " select * from CT_DS_STUDENT where 1=1 ";
			buf.append(strsql);
			List<Map<String, String>> errorStu = new ArrayList<>();//导入失败的学员
			List<Map<String, String>> successStu = new ArrayList<>();//导入成功的学员
			Map<String,List<Map<String, String>>> accomplishmsg=new HashMap<>();//最后的信息
			int count=0;
			int item =0;//记录插入成功的条数
			int[] numFseq=new int[testTime2.size()];
			for(int i=0;i<numFseq.length;i++){
				numFseq[i]=0;
			}
			//Map<String,String> countmap=new HashMap<>();//记录导入成功的数据
			for (int i = 1; i <= map.size(); i++) {
				List<StudentImport> stus = map.get("" + i + "");
				//countmap.put(i+"", stus.size()+"");
				for (int j = 0; j < stus.size(); j++) {
					Map<String, String> mapParameter = new HashMap<>();// 构造Map参数
					mapParameter.put("cerno", stus.get(j).getCerno());
					mapParameter.put("cartype", stus.get(j).getType());
					mapParameter.put("companyId", user.getFcompanyid());
					//mapParameter.put("successcount",stus.size()+"");//成功导入的数据
					mapParameter.put("sumcount",stus.size()+"");//总的记录数据
					List<Map<String, String>> stuMap = new ArrayList<>();
					stuMap = studentImportService.findByCerno(mapParameter);// 获取学员信息
					if (stuMap.size() == 0 || stuMap == null) {
						count++;
						mapParameter.put("name", stus.get(j).getStuName());
						mapParameter.put("mes","未有该学员信息");
						mapParameter.put("successcount",(stus.size()-count)+"");
						errorStu.add(mapParameter);
						continue;
					}

					//判断是否有科目二、科目三登记,如果没有，则记录错误并continue;
					if (stus.get(j).getCourse().equals("科目二")) {
						//数据表 CT_DS_SUBJECTTWOREGENTRY
						List<Map<String, String>> stuMapTwo = new ArrayList<>();
						stuMapTwo = studentImportService.findByCernoTwo(mapParameter);// 获取学员信息
						if (stuMapTwo.size() == 0 || stuMapTwo == null) {
							mapParameter.put("name", stus.get(j).getStuName());
							mapParameter.put("mes","未有该学员科目二登记信息");
							mapParameter.put("successcount",(stus.size()-count)+"");
							errorStu.add(mapParameter);
							continue;
						}

					} else if (stus.get(j).getCourse().indexOf("科目三")>=0) {
						//数据表 CT_DS_SUBJECTTHREEREGENTRY
						List<Map<String, String>> stuMapThree = new ArrayList<>();
						stuMapThree = studentImportService.findByCernoThree(mapParameter);// 获取学员信息
						if (stuMapThree.size() == 0 || stuMapThree == null) {
							mapParameter.put("name", stus.get(j).getStuName());
							mapParameter.put("mes","未有该学员科目三登记信息");
							mapParameter.put("successcount",(stus.size()-count)+"");
							errorStu.add(mapParameter);
							continue;
						}

					}
					//新生序号
					item++;
					Map<String, Object> subject = new HashMap<>();
                     //按照比较生成对应子表的fid
					for(int t=0;t<testTime2.size();t++){
								if(stus.get(j).getDate().equals(testTime2.get(t))){
									subject.put("FPARENTID", childerFid[t]);
									numFseq[t]++;
									subject.put("FSEQ",numFseq[t]+"");
								}
					}


					subject.put("CFSTUDENTID", stuMap.get(0).get("FID"));
					subject.put("CFSTUDENTNO", stuMap.get(0).get("FNUMBER"));
					subject.put("CFIDCARDNO", stuMap.get(0).get("CFIDCARDNO"));
					subject.put("CFSEX", stuMap.get(0).get("CFSEX"));
					subject.put("CFMOBILE", stuMap.get(0).get("CFSJHM"));
					subject.put("CFPHONE", stuMap.get(0).get("CFLXDH"));
					subject.put("CFSQZJCX", stuMap.get(0).get("CFSLZJCXDH"));
					subject.put("CFBDYD", stuMap.get(0).get("CFLOCAL"));
					subject.put("CFCLAZZNAME", stuMap.get(0).get("CFCLAZZNAME"));
					//System.out.println("班级："+subject.get("CFCLAZZNAME"));
					//cfclazzName


					//大等于90分算合格
					if(stus.get(j).getScore() ==null){
						stus.get(j).setScore("0");
					}
					if (stus.get(j).getScore().equals("90")) {
						subject.put("CFEXAMRESULT", "1");//合格
					} else if (stus.get(j).getScore().equals("0")) {//不合格
						subject.put("CFEXAMRESULT", "2");
					} else if (stus.get(j).getScore().equals("-1")) {//缺考
						subject.put("CFEXAMRESULT", "3");
					}
					subject.put("CFZKZMH", stus.get(j).getZkzNum());
					subject.put("CFRECEIVABLE", stuMap.get(0).get("CFAMOUNT"));
					subject.put("CFRECEIVED", stuMap.get(0).get("CFRECEIVED"));
					subject.put("CFARREARAGE", stuMap.get(0).get("CFARREARAGE"));
					if(stuMap.get(0).get("CFFAVORABLE") == null){
						stuMap.get(0).put("CFFAVORABLE", "0");
					}
					subject.put("CFFAVORABLE", stuMap.get(0).get("CFFAVORABLE"));
					subject.put("fid",GetFid.UUIDIntoFid());
					if (stus.get(j).getCourse().equals("科目一")) {
						subject.put("from", "CT_DS_SUBJECTONERESULTENTRY");
						studentImportService.addSubsidiaryOne(subject);
					} else if (stus.get(j).getCourse().equals("科目二")) {
						subject.put("from", "CT_DS_SUBJECTTWORESULTENTRY");
						studentImportService.addSubsidiaryTwo(subject);
					} else if (stus.get(j).getCourse().indexOf("科目三")>=0) {
						subject.put("from", "CT_DS_SUBJECTTHREERESULTENTRY");
						studentImportService.addSubsidiaryThr(subject);
					} /*else {
						// 抛出一个异常
						subject.put("from", "CT_DS_SUBJECTTHREERESULTENTRY");
						studentImportService.addSubsidiaryThr(subject);
					}*/
					//添加导入成功学员信息
					mapParameter.put("name", stus.get(j).getStuName());
					successStu.add(mapParameter);

					// 修改状态
					//1根据身份证和车型查找该学员的细信息
					Student _student=studentMapper.selStudentMesgByIdcardAndCarType(stus.get(j).getCerno(),stus.get(j).getType(),user.getFcompanyid()).get(0);
					//System.out.println("是否存在该学员信息: "+_student);
					Student update_student=new Student();
					if(_student !=null){
						update_student.setFid(_student.getFid());
						/*if(_student.getCfkmykss()>= 5L){
							update_student.setCfstudentstate(14L);//状态改为作废
							//修改学员信息
							studentMapper.updateStudent(update_student);
							break;//结束当前循环
						}*/
						if(_student.getCfkmykss()==null){//科目一考试次数
							_student.setCfkmykss(0L);
						}
						if(_student.getCfkmekss()==null){//科目二考试次数
							_student.setCfkmekss(0L);
						}
						if(_student.getCfkmskss()==null){//科目三考试次数
							_student.setCfkmskss(0L);
						}
						if(_student.getCfkmyqkcs()==null){//科目一缺考次数
							_student.setCfkmyqkcs(0L);
						}
						if(_student.getCfkmeqkcs()==null){//科目二缺考次数
							_student.setCfkmeqkcs(0L);
						}
						if(_student.getCfkmsqkcs()==null){//科目三缺考次数
							_student.setCfkmsqkcs(0L);
						}

						//判断科目一
						if(stus.get(j).getCourse().indexOf("科目一") > 	-1){
							if (stus.get(j).getScore().equals("90")){//科目一通过
								update_student.setCfstudentstate(2L);//状态改为科目一通过
								update_student.setCfkmykss(_student.getCfkmykss()+1L);//科目一考试次数加一
								update_student.setCfkmyherq(ks_Date); //科目一通过时间
							}else if(stus.get(j).getScore().equals("0")){//科目一未通过
								/*//判断考试次数是否超过5次 如果超过5次则作废
								if(_student.getCfkmykss()+1L >= 5L){
									update_student.setCfstudentstate(14L);//状态改为作废
								}else{*/
								update_student.setCfstudentstate(3L);//状态改为科目一未通过
								update_student.setCfkmykss(_student.getCfkmykss()+1L);//科目一考试次数加一
								update_student.setCfkmybjgsj(ks_Date);//科目一未通过时间
								//}
							}else {//缺考
								//if(_student.getCfkmykss()+1L >= 5L){
								//update_student.setCfstudentstate(14L);//状态改为作废
								//}else{
								update_student.setCfstudentstate(3L);//状态改为科目一未通过
								update_student.setCfkmykss(_student.getCfkmykss()+1L);//科目一考试次数加一
								update_student.setCfkmyqkcs(_student.getCfkmyqkcs()+1L);//缺考次数加一
								update_student.setCfkmybjgsj(ks_Date);//科目一未通过时间
								//}
							}
						}

						//判断科目二
						if(stus.get(j).getCourse().indexOf("科目二") > 	-1){
							if(stus.get(j).getScore().equals("90")){//科目二通过 但是需要判断该学员是否开始过科目三
								update_student.setCfkmekss(_student.getCfkmekss()+1L);//科目二考试次数加一
								update_student.setCfkmehgrq(ks_Date);//科目二通过时间
								if(_student.getCfkmshgrq() !=null){//先判断科目三通过时间是否存在
									update_student.setCfstudentstate(7L);//科目二通过 科目三通过
								}else if(_student.getCfkmsbjgsj() !=null || _student.getCfkmsksqksj()!=null){//判断该学员的未通过科目三时间 和缺考时间
									update_student.setCfstudentstate(8L);//科目二通过 科目三未通过
								}else{//如果全部都为空则说明该学员未开始过科目三
									update_student.setCfstudentstate(4L);//科目二通过 科目三未开始
								}
							}else if(stus.get(j).getScore().equals("0")){//科目二未通过
								/*if(_student.getCfkmekss()+1L >= 5L){
									update_student.setCfstudentstate(14L);//状态改为作废
								}else{*/
								update_student.setCfkmekss(_student.getCfkmekss()+1L);//科目二考试次数加一
								update_student.setCfkmebjgsj(ks_Date);//科目二未通过时间
								if(_student.getCfkmshgrq() !=null){//先判断科目三通过时间是否存在
									update_student.setCfstudentstate(17L);//科目二未通过 科目三通过
								}else if(_student.getCfkmsbjgsj() !=null || _student.getCfkmsksqksj()!=null){//判断该学员的未通过科目三时间 和缺考时间
									update_student.setCfstudentstate(20L);//科目二未通过 科目三未通过
								}else{//如果全部都为空则说明该学员未开始过科目三
									update_student.setCfstudentstate(5L);//科目二未通过 科目三未开始
								}
								//}

							}else{//科目二缺考
								/*if(_student.getCfkmekss()+1L >= 5L){
									update_student.setCfstudentstate(14L);//状态改为作废
								}else{*/
								update_student.setCfkmekss(_student.getCfkmekss()+1L);//科目二考试次数加一
								update_student.setCfkmeqkcs(_student.getCfkmeqkcs() == null ? 0:_student.getCfkmeqkcs() +1L);//科目二缺考次数加一
								update_student.setCfkmebjgsj(ks_Date);//科目二未通过时间
								update_student.setCfkmeksqksj(ks_Date);//科目二缺考时间
								if(_student.getCfkmshgrq() !=null){//先判断科目三通过时间是否存在
									update_student.setCfstudentstate(17L);//科目二未通过 科目三通过
								}else if(_student.getCfkmsbjgsj() !=null || _student.getCfkmsksqksj()!=null){//判断该学员的未通过科目三时间 和缺考时间
									update_student.setCfstudentstate(20L);//科目二未通过 科目三未通过
								}else{//如果全部都为空则说明该学员未开始过科目三
									update_student.setCfstudentstate(5L);//科目二未通过 科目三未开始
								}
								//}

							}
						}

						//判断科目三
						if(stus.get(j).getCourse().indexOf("科目三") > 	-1){
							if(stus.get(j).getScore().equals("90")){//科目三通过 但是需要判断该学员是否开始过科目二
								update_student.setCfkmskss(_student.getCfkmskss()+1L);//科目三考试次数加一
								update_student.setCfkmshgrq(ks_Date);//科目三通过时间
								if(_student.getCfkmehgrq() !=null){//先判断科目二通过时间是否存在
									update_student.setCfstudentstate(7L);//科目三通过 科目二通过
								}else if(_student.getCfkmebjgsj() !=null || _student.getCfkmeksqksj()!=null){//判断该学员的未通过科目二时间 和缺考时间
									update_student.setCfstudentstate(17L);//科目三通过 科目二未通过
								}else{//如果全部都为空则说明该学员未开始过科目三
									update_student.setCfstudentstate(19L);//科目三通过 科目二未通过
								}
							}else if(stus.get(j).getScore().equals("0")){//科目三未通过
								/*if(_student.getCfkmskss()+1L >= 5L){
									update_student.setCfstudentstate(14L);//状态改为作废
								}else{*/
								update_student.setCfkmskss(_student.getCfkmskss()+1L);//科目三考试次数加一
								update_student.setCfkmsbjgsj(ks_Date);//科目三未通过时间
								if(_student.getCfkmehgrq() !=null){//先判断科目二通过时间是否存在
									update_student.setCfstudentstate(8L);//科目三未通过 科目二通过
								}else if(_student.getCfkmebjgsj() !=null || _student.getCfkmeksqksj()!=null){//判断该学员的未通过科目二时间 和缺考时间
									update_student.setCfstudentstate(20L);//科目三未通过 科目二未通过
								}else{//如果全部都为空则说明该学员未开始过科目二
									update_student.setCfstudentstate(21L);//科目三未通过 科目二未开始
								}
								//}

							}else{//科目三缺考
								/*if(_student.getCfkmskss()+1L >= 5L){
									update_student.setCfstudentstate(14L);//状态改为作废
								}else{*/
								update_student.setCfkmskss(_student.getCfkmskss()+1L);//科目三考试次数加一
								update_student.setCfkmsqkcs(_student.getCfkmsqkcs() == null ? 0:_student.getCfkmsqkcs() +1L);
								update_student.setCfkmsbjgsj(ks_Date);//科目三未通过时间
								update_student.setCfkmsksqksj(ks_Date);//科目三缺考时间
								if(_student.getCfkmehgrq() !=null){//先判断科目二通过时间是否存在
									update_student.setCfstudentstate(8L);//科目三未通过 科目二通过
								}else if(_student.getCfkmebjgsj() !=null || _student.getCfkmeksqksj()!=null){//判断该学员的未通过科目二时间 和缺考时间
									update_student.setCfstudentstate(20L);//科目三未通过 科目二未通过
								}else{//如果全部都为空则说明该学员未开始过科目二
									update_student.setCfstudentstate(21L);//科目三未通过 科目二未开始
								}
								//}
							}
						}
						//修改学员信息
						studentMapper.updateStudent(update_student);
					}
				}
			/*System.out.println(buf.toString());
			List<Map<String, String>> stuMap;
			if(strsql.equals(buf.toString()))
			{
				stuMap=studentImportService.stuShow(" select * from CT_DS_STUDENT where 1<>1 ");
			}
			else
			{
				String Str2=buf.toString();
				Str2=Str2.replace("1=1  or","1=1  and");
				stuMap = studentImportService.stuShow(Str2);
			}*/
				accomplishmsg.put("success", successStu);
				accomplishmsg.put("failed", errorStu);
				//System.out.println("成功的数据条数:"+successMap.size());
				//System.out.println("失败的数据条数:"+errorStu.size());
			}
			//如果出现导入数据全部失败则需要删除主表
			if(accomplishmsg.get("success").size() == 0){
				for(int i=0;i<childerFid.length;i++){
					if (map.get("1").get(0).getCourse().equals("科目一")) {
						subjectOneResultService.deleteSubjectOneResult(childerFid[i]);
					} else if (map.get("1").get(0).getCourse().equals("科目二")) {
						subjectTwoResultService.deleteSubjectTwoResult(childerFid[i]);
					} else if (map.get("1").get(0).getCourse().indexOf("科目三")>=0) {
						subjectThreeResultService.deleteSubjectThreeResult(childerFid[i]);
					}
		      }
			}
			return accomplishmsg;

		}
	}
	/**
	 * Excel上传
	 * @param request
	 * @throws Exception
	 */
	public  void uploadExcel(HttpServletRequest request, MultipartFile file,String fileName)throws Exception{
		String path=request.getServletContext().getRealPath("/")+"xls/";
		File fileUpload=new File(path);
		if(!fileUpload.exists()){
			fileUpload.mkdirs();
		}
		file.transferTo(new File(path+fileName));
		//FileCopyUtils.copy(file.getBytes(), fileUpload);
		/*String path=request.getServletContext().getRealPath("/");
		InputStream input=new FileInputStream(image);
		OutputStream out=new FileOutputStream(path+File.separatorChar+imageFileName);
		IOUtils.copy(input, out);
		out.flush();
		out.close();
		input.close();*/
	}
}
